﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data.Common;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace com.lover.dbcommon
{
    public class MySQLHelper
    {
        private static string _defaultConnString = "";
        private static MySQLHelper _defaultInstance;
        public static MySQLHelper getInstance(string connString = null)
        {
            if (connString == null)
            {
                if (_defaultInstance == null) _defaultInstance = new MySQLHelper(_defaultConnString);
                return _defaultInstance;
            }
            else return new MySQLHelper(connString);
        }
        private string _connString;
        private MySQLHelper(string connString) { _connString = connString; }
        public int exeNonQuery(string cmdText, CommandType cmdType = CommandType.Text, MySqlParameter[] cmdParams = null)
        {
            MySqlCommand cmd = new MySqlCommand();
            using(MySqlConnection conn = new MySqlConnection(_connString))
            {
                prepareCommand(cmd, conn, cmdText, cmdType, null, cmdParams);
                int lines = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return lines;
            }
        }
        public object exeScalar(string cmdText, CommandType cmdType = CommandType.Text, MySqlParameter[] cmdParams = null)
        {
            MySqlCommand cmd = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(_connString))
            {
                prepareCommand(cmd, conn, cmdText, cmdType, null, cmdParams);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }
        public MySqlDataReader exeReader(string cmdText, CommandType cmdType = CommandType.Text, MySqlParameter[] cmdParams = null)
        {
            MySqlCommand cmd = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(_connString);
            try
            {
                prepareCommand(cmd, conn, cmdText, cmdType, null, cmdParams);
                MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                if (conn != null) conn.Close();
                throw;
            }
        }
        public MySqlParameter createParameter(string pName, object value, MySqlDbType dbType, int size=0, ParameterDirection direction = ParameterDirection.Input)
        {
            MySqlParameter p;
            if (size > 0) p = new MySqlParameter(pName, dbType, size);
            else p = new MySqlParameter(pName, dbType);

            p.Direction = direction;
            if (!(direction == ParameterDirection.Output && value == null)) p.Value = value;

            return p;
        }
        private void prepareCommand(MySqlCommand cmd, MySqlConnection conn, string cmdText,
            CommandType cmdType=CommandType.Text,  MySqlTransaction trans=null, MySqlParameter[] cmdParams = null)
        {
            if (conn.State != ConnectionState.Open) { conn.Open(); }
            
            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null) { cmd.Transaction = trans; }

            if (cmdParams != null && cmdParams.Length > 0) { foreach (MySqlParameter p in cmdParams) cmd.Parameters.Add(p); }
        }
    }
    
}
